package dao;

import po.Book;
import po.Collect;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class CollectDao {
    /**
     * 根据用户编号和图书编号查询收藏记录
     */
    public Integer selectByProductAndUserId(String isbn,String phone){
        /**
         * 基本类型只有8个，不能参与java对象开发，所以每个基本类型都有一个包装类，包装类默认值为null
         * int ->>Integer
         * double ->> Double
         */
        Integer rid = null;
        Connection connection = DBUtils.getConn();
        String sql ="select rid from tb_collect where user_id=? and product=?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,phone);
            preparedStatement.setString(2,isbn);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                rid = resultSet.getInt("rid");

            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        DBUtils.close(preparedStatement,connection);
        }
        return rid;
    }

    public Integer insert(Collect collect) {
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        Integer row = null;
        String sql = "insert into tb_collect values(null,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,collect.getUserid());
            preparedStatement.setString(2,collect.getProduct());
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
    return row;
    }

    public Integer delete(Collect collect) {
        Connection connection = DBUtils.getConn();
        PreparedStatement preparedStatement = null;
        Integer row = null;
        String sql = "delete from tb_collect where user_id=? and product=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,collect.getUserid());
            preparedStatement.setString(2,collect.getProduct());
            row = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.close(preparedStatement,connection);
        }
        return row;
    }

    public ArrayList<Book> findByUserId(String phone) {
        Connection connection = DBUtils.getConn();
        String sql ="select book.*,collect.product,collect.rid from tb_book book " +
                "inner join tb_collect collect on book.isbn = collect.product " +
                "where collect.user_id=? order by collect.rid desc";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Book b = null;
        ArrayList<Book> list = new ArrayList<Book>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,phone);
            resultSet = preparedStatement.executeQuery();
           while (resultSet.next()){
               b = new Book();
               b.setAuthor(resultSet.getString("author"));
               b.setEdition(resultSet.getInt("edition"));
               b.setForm(resultSet.getString("form"));
               b.setFormat(resultSet.getString("format"));
               b.setIsbn(resultSet.getString("isbn"));
               b.setPackaging(resultSet.getString("packaging"));
               b.setPages(resultSet.getInt("pages"));
               b.setPress(resultSet.getString("press"));
               b.setPrice(resultSet.getDouble("price"));
               b.setPublished(resultSet.getDate("published"));
               b.setTitle(resultSet.getString("title"));
               b.setWords(resultSet.getInt("words"));
               list.add(b);
           }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            if (resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            DBUtils.close(preparedStatement,connection);
        }
        return list;
    }
}
